﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using Ebiz.libs;
using System.Web.Script.Services;
using System.Web.Services;
using System.IO;
using System.Data.OleDb;

namespace Ebiz
{
    public partial class EditBulkUpload : System.Web.UI.Page
    {
        public string AppList = "";
        enum BulkMode
        {
            BulkMaster, BulkDetail
        }
        enum RequestMode { 
            Master, Files, Customer, Product, Approval
        }
        public string strWarning = "";
        protected void Page_Load(object sender, EventArgs e){
            if (Request.QueryString["cl"] != null &&
                !(string.IsNullOrEmpty(Request.QueryString["cl"])))
            {
                string CLNumber = Request.QueryString["cl"];
                string ExcelConnection = "";
                if (!IsPostBack){
                    if (isBulkUploading()){
                        string showErr = "<script>alert('Bulk Upload is in Progress!\n Please try again');";
                        showErr += "window.location='Home.aspx';</script>";
                        Response.Write(showErr);
                        SetBulkStatus(0);
                    }else{
                        //Check if Logged Person is the owner of this CL
                        string LoggedPerson = Func.GetUserInfo(Func.ShowInfo._UserID);
                        if (isValidCL(CLNumber, LoggedPerson) == true){
                            //Load Previous CL Data here ----------------
                            txtCLNumber.Text = CLNumber;
                            LoadCLData(CLNumber);
                        }else { //Possible attack
                            Session["ErrCode"] = "ES-404";
                            Response.Redirect("ErrorPage.aspx", true);
                        }
                    }
                }else if (IsPostBack){
                    //Show Warning Message
                    ShowWarningMsg();

                    if (fileCL.HasFile){
                        string TPath = Server.MapPath("~/images/upload/");
                        if (!Directory.Exists(TPath)){
                            Directory.CreateDirectory(TPath);
                        }
                        int tmpFSize = fileCL.PostedFile.ContentLength;
                        double fSize = Math.Round((double)(tmpFSize / 1024), 2, MidpointRounding.AwayFromZero);
                        double RealFSize = (fSize > 1024) ? Math.Round((fSize / 1024), 2, MidpointRounding.AwayFromZero) : fSize;
                        string fUnit = (fSize > 1024) ? " MB" : " KB";

                        if (fSize > 5120){
                            string showErr = "<script>alert('Error: File Size Exeeds Limit of 5MB!');return false;</script>";
                            Response.Write(showErr);
                        }else{
                            string DistributorCode = hidDistributor.Value;
                            string CLNum = txtCLNumber.Text;
                            string ApprovalList = txtAppList.Value;

                            string tmpFileName = fileCL.PostedFile.FileName;
                            string fixFileName = RenameIfExist(tmpFileName);
                            string[] nFile = fixFileName.Split(new string[] { "\\" }, StringSplitOptions.None);
                            string fileName = TPath + nFile[nFile.Count() - 1];
                            string fileSize = fSize.ToString();
                            string fType = fileCL.PostedFile.ContentType.ToString();

                            ExcelConnection = setExcelConn(fileName, fType);
                            if (ExcelConnection == string.Empty || ExcelConnection == "INVALID"){
                                string showErr = "<script>alert('Error: Invalid File Type!');window.location='BulkUpload.aspx';</script>";
                                Response.Write(showErr);
                            }else{
                                SetBulkStatus(1); //Lock Bulk Tables
                                //Delete All Previous Data on Bulk Tables
                                DeleteBulk(BulkMode.BulkDetail);
                                DeleteBulk(BulkMode.BulkMaster);

                                fileCL.SaveAs(fileName);
                                Dictionary<string, object> dctResult = UploadData(ExcelConnection, fileName, CLNum);
                                int bulkStatus = Convert.ToInt32(dctResult["Status"].ToString());
                                if (bulkStatus == 1){
                                    //Delete All Previous Data on Request Tables
                                    DeleteRequest(CLNumber, RequestMode.Approval);
                                    DeleteRequest(CLNumber, RequestMode.Customer);
                                    DeleteRequest(CLNumber, RequestMode.Product);
                                    DeleteRequest(CLNumber, RequestMode.Files);
                                    DeleteRequest(CLNumber, RequestMode.Master);

                                    //Delete Previous File
                                    DeletePrevFile();

                                    Dictionary<string, string> dctFileInfo = new Dictionary<string, string>();
                                    dctFileInfo.Add("CLNum", CLNum);
                                    dctFileInfo.Add("FileLocation", "images/upload/");
                                    dctFileInfo.Add("FileName", fixFileName);
                                    dctFileInfo.Add("FileType", GetFileExtension(fType));
                                    dctFileInfo.Add("FileSize", fileSize);

                                    SaveBulkMaster(CLNum, DistributorCode, ApprovalList, dctFileInfo);
                                }else{
                                    //TO DO: Rollback here
                                    SetBulkStatus(0);
                                    string showErr = "<script>alert('Error: Bulk Upload Failed!');window.location='BulkUpload.aspx';</script>";
                                    Response.Write(showErr);
                                }
                            }
                        }
                    }
                }
            }else{ // Posible attack
                Session["ErrCode"] = "ES-404";
                Response.Redirect("ErrorPage.aspx", true);
            }
        }

        private void ShowWarningMsg() {
            strWarning = "<p>Please wait, processing bulk upload...</p>" +
                "<div style='color:Red;font-weight:bold;'>WARNING:</div>" +
                "<div style='color:Red;'>" +
                "    While Uploading, <strong>DO NOT</strong>:<br />" +
                "    <ul>" +
                "        <li>Click any buttons</li>" +
                "        <li>Close or Leave the page</li>" +
                "        <li>Turn of your computer</li>" +
                "    </ul>" +
                "</div>";
        }

        private static bool isValidCL(string CLNumber, string OwnerID) {
            bool result = false;

            string DBName = DBUtilities.GetDBName("devCon");
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using(cn){
                try {
                    cn.Open();

                    string qry = "SELECT COUNT(CLNum) FROM " + DBName + ".dbo.tbl_Request " +
                        "WHERE (CLNum = @CLNum) AND (CreatedBy = @CreatedBy) " + 
                        "AND (StatusCode = '213')";
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("CLNum", CLNumber);
                    cmd.Parameters.AddWithValue("CreatedBy", OwnerID);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows) {
                        if (rdr.Read()) {
                            int RowLen = (int)rdr.GetValue(0);
                            if (RowLen > 0) {
                                result = true;      
                            }
                        }
                    }
                }catch (Exception ex) {
                    string errMsg = ex.Message;
                }finally {
                    if (cn.State == ConnectionState.Open) {
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private void LoadCLData(string CLNumber) {
            LoadDistributor(CLNumber);
            LoadFileList(CLNumber);
            //LoadCLApproval(CLNumber);
        }

        private void LoadDistributor(string CLNumber) {
            string DBName = DBUtilities.GetDBName("devCon");
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();

                    string qry = "SELECT tbl_Request.DistributorCode, tbl2.DistributorName " +
                        "FROM Ebiz.dbo.tbl_Request " +
                        "INNER JOIN " + DBName + ".dbo.mstDistributor AS tbl2 " +
                        "ON tbl2.DistributorCode = tbl_Request.DistributorCode " +
                        "WHERE (tbl_Request.CLNum = @CLNum)";

                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("CLNum", CLNumber);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        if (rdr.Read()){
                            txtDistributor.Text = rdr.GetValue(1).ToString();
                            hidDistributor.Value = rdr.GetValue(0).ToString();
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open) {
                        cn.Close();
                    }
                }
            }
        }

        private void LoadFileList(string CLNumber) {
            string DBName = DBUtilities.GetDBName("devCon");
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();

                    string qry = "SELECT FileLocation, FileName " +
                        "FROM  " + DBName + ".dbo.tbl_RequestFiles " +
                        "WHERE (tbl_RequestFiles.CLNum = @CLNum)";

                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("CLNum", CLNumber);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        if (rdr.Read()){
                            lblPrevFile.Text = rdr.GetValue(1).ToString();
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
        }

        /* TO DO: For next patch ------
        private void LoadCLApproval(string CLNumber) {
            string DBName = DBUtilities.GetDBName("devCon");
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();

                    string qry = "";
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        while(rdr.Read()){

                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open) {
                        cn.Close();
                    }
                }
            }
        }
        */

        private static Dictionary<string, object> UploadData(string ExcelCNString, string ExcelPath,
            string CLNumber){

            Dictionary<string, object> result = new Dictionary<string, object>();

            int responseStatus = 0;
            string responseMsg = "";

            OleDbConnection ExcelCN = new OleDbConnection(ExcelCNString);
            using (ExcelCN){
                ExcelCN.Open();

                string sheetName = ExcelCN.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
                string ExcelSelect = "SELECT '" + CLNumber + "' AS CLNum, * ";
                ExcelSelect += "FROM [" + sheetName + "]";
                OleDbCommand ExcelCmd = new OleDbCommand(ExcelSelect, ExcelCN);
                OleDbDataReader ExcelReader = ExcelCmd.ExecuteReader();

                string DBName = DBUtilities.GetDBName("devCon");
                string strCon = DBUtilities.strConnection("DevCon");
                SqlConnection cn = new SqlConnection(strCon);
                using (cn){
                    try{
                        cn.Open();

                        SqlTransaction sqlTransaction = cn.BeginTransaction();
                        SqlBulkCopy bulkCopy = new SqlBulkCopy(cn, SqlBulkCopyOptions.Default, sqlTransaction);
                        using (bulkCopy){
                            bulkCopy.DestinationTableName = DBName + ".dbo.tbl_BulkDetail";

                            bulkCopy.ColumnMappings.Add(0, "CLNum");
                            bulkCopy.ColumnMappings.Add(1, "ProductCode");
                            bulkCopy.ColumnMappings.Add(2, "ProdDivision");
                            //bulkCopy.ColumnMappings.Add(3, "ProductName");
                            bulkCopy.ColumnMappings.Add(4, "Discount");
                            bulkCopy.ColumnMappings.Add(5, "Quantity");
                            bulkCopy.ColumnMappings.Add(6, "CustGroup");
                            //bulkCopy.ColumnMappings.Add(7, "CustCode");
                            bulkCopy.ColumnMappings.Add(8, "CSCodeAZI");
                            bulkCopy.ColumnMappings.Add(9, "CustName");
                            bulkCopy.ColumnMappings.Add(10, "CustAddress");

                            bulkCopy.BatchSize = 100;
                            try{
                                bulkCopy.WriteToServer(ExcelReader);
                                sqlTransaction.Commit();

                                responseStatus = 1;
                                responseMsg = "Success";

                            }catch (Exception ex){
                                sqlTransaction.Rollback();
                                responseMsg = ex.Message;
                            }finally{
                                bulkCopy.Close();
                            }
                        }
                        ExcelReader.Close();
                    }catch (Exception ex){
                        responseMsg = ex.Message;
                    }finally{
                        if (cn.State == ConnectionState.Open){
                            cn.Close();
                        }
                    }
                }
            }
            result.Add("Status", responseStatus);
            result.Add("Message", responseMsg);

            return result;
        }

        private void SaveBulkMaster(string CLNumber, string DistributorCode,
            string ApprovalList, Dictionary<string, string> dctFile){
            string Token = Func.GetUserInfo(Func.ShowInfo._SSID);
            string[] appList = ApprovalList.Split(',');
            int appListLen = appList.Length;
            string ApprovalCurrent = appList[0];
            string ApprovalNext = appList[1];
            int ApprovalMax = appList.Length;
            string CreatedBy = Func.GetUserInfo(Func.ShowInfo._UserID);

            string DBName = DBUtilities.GetDBName("devCon");
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            string sql = "INSERT INTO " + DBName + ".dbo.tbl_BulkMaster (CLNum, DistributorCode, " +
                "Channel, IDControl, Value1, ApprovalCurrent, ApprovalNext, ApprovalStep, ApprovalMax, " +
                "CreatedBy, CreatedDate, StatusCode) VALUES " +
                "('" + CLNumber + "', '" + DistributorCode + "', 'HOS', 'CHAN', 'HOS', '" + ApprovalCurrent +
                "', '" + ApprovalNext + "', 1, " + ApprovalMax + ", '" + CreatedBy + "', getdate(), '210')";

            if (ExecQuery(sql) > 0){
                //Copy view mapBulkCopy to: tbl_Request, tbl_RequestProduct, tbl_RequestCustomer
                //tbl_RequestApproval, tbl_RequestFiles
                List<string> appPersonList = appList.ToList<string>();
                SaveRequestCL(CLNumber, appPersonList, dctFile);

            }//Else TO DO: Rollback here
        }

        private void SaveRequestCL(string CLNumber, List<string> ApprovalList,
            Dictionary<string, string> dctFile){

            string Token = Func.GetUserInfo(Func.ShowInfo._SSID);
            string CreatedBy = Func.GetUserInfo(Func.ShowInfo._UserID);

            string DBName = DBUtilities.GetDBName("devCon");
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            string sql = "INSERT INTO " + DBName + ".dbo.tbl_Request (CLNum, DistributorCode, " +
                "CLCustName, CustomerGroup, Channel, DateRequest, DateStart, DateEnd, " +
                "DiscReason, Token, IDControl, Value1, ApprovalCurrent, ApprovalNext, " +
                "ApprovalStep, ApprovalMax, CreatedBy, CreatedDate, StatusCode) " +
                "SELECT TableSource.CLNum, TableSource.DistributorCode, " +
                "'As Attached', 'As Attached', 'HOS', getdate(), " +
                "(SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)), " +
                "(SELECT DATEADD(dd,-1,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0)))), " +
                "'As Attached', '" + Token + "', 'CHAN', 'HOS', " +
                "TableSource.ApprovalCurrent, TableSource.ApprovalNext, " +
                "TableSource.ApprovalStep, TableSource.ApprovalMax, '" +
                CreatedBy + "', getdate(), '210' " +
                "FROM " + DBName + ".dbo.tbl_BulkMaster As TableSource";

            if (ExecQuery(sql) > 0){
                //Copy view mapBulkCopy to: tbl_Request, tbl_RequestProduct, tbl_RequestCustomer
                //tbl_RequestApproval, tbl_RequestFiles

                SaveRequestFile(CLNumber, ApprovalList, dctFile);

            }else{
                //TODO else Rollback here
                string showErr = "<script>alert('" + sql + "');window.location='BulkUpload.aspx';</script>";
                Response.Write(showErr);
            }
        }

        private void SaveRequestFile(string CLNumber, List<string> ApprovalList,
            Dictionary<string, string> dctFile){

            string DBName = DBUtilities.GetDBName("devCon");

            string sql = "INSERT INTO " + DBName + ".dbo.tbl_RequestFiles " +
                "(CLNum, FileLocation, FileName, FileType, FileSize, StatusCode) VALUES " +
                "('" + dctFile["CLNum"] + "', @FileLocation, @FileName, '" +
                dctFile["FileType"] + "', '" + dctFile["FileSize"] + "', '101')";
            Dictionary<string, object> dctParams = new Dictionary<string, object>();
            dctParams.Add("FileLocation", dctFile["FileLocation"]);
            dctParams.Add("FileName", dctFile["FileName"]);

            if (ExecQuery(sql, dctParams) > 0){
                SaveApproval(CLNumber, ApprovalList);
            }else{
                //TODO else Rollback here
                string showErr = "<script>alert('" + sql + "');window.location='BulkUpload.aspx';</script>";
                Response.Write(showErr);
            }
        }

        private void SaveApproval(string CLNumber, List<string> ApprovalList){

            bool isError = false;

            string Token = Func.GetUserInfo(Func.ShowInfo._SSID);
            string DBName = DBUtilities.GetDBName("devCon");
            int MaxStep = ApprovalList.Count;
            for (int idx = 0; idx < MaxStep; idx++){
                int StepSeq = (idx + 1);
                string CurrentStep = ApprovalList[idx];
                string EmailAddress = GetEmailAddress(CurrentStep);

                string NextStep = "";
                if (idx < (MaxStep - 1)){
                    NextStep = ApprovalList[(idx + 1)];
                }else{
                    NextStep = "0";
                }

                string sql = "INSERT INTO " + DBName + ".dbo.tbl_RequestApproval (CLNum, StepSeq, " +
                    "CurrentStep, NextStep, EmailAddress, Token, StatusCode) " +
                    "VALUES (@CLNum, " + StepSeq + ", @CurrentStep, @NextStep, @EmailAddress, " +
                    "'" + Token + "', '210')";
                Dictionary<string, object> dctParams = new Dictionary<string, object>();
                dctParams.Add("CLNum", CLNumber);
                dctParams.Add("CurrentStep", CurrentStep);
                dctParams.Add("NextStep", NextStep);
                dctParams.Add("EmailAddress", EmailAddress);

                int ExecResult = ExecQuery(sql, dctParams);
                if (ExecResult < 1){
                    isError = true;
                    break;
                }
            }

            if (!isError){
                //Redirect Page for Advanced Processing
                string PostTarget = "PostBulk.aspx?postbulk=" + CLNumber;
                Response.Redirect(PostTarget, true);
            }else{
                //TO DO: Rollback here
                string showErr = "<script>alert('Error: Bulk Upload Failed!');window.location='BulkUpload.aspx';</script>";
                Response.Write(showErr);
            }
        }

        private string GetEmailAddress(string UserID){
            string result = "";
            string DBName = DBUtilities.GetDBName("devCon");
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();

                    string sql = "SELECT Email FROM " + DBName + ".dbo.UserAccounts " +
                        "WHERE (UserName = @UserID)";

                    SqlCommand cmd = new SqlCommand(sql, cn);
                    cmd.Parameters.AddWithValue("UserID", UserID);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        if (rdr.Read()){
                            result = rdr.GetValue(0).ToString();
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private void DeleteBulk(BulkMode bulkMode){
            string DBName = DBUtilities.GetDBName("devCon");
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            string sql = "";
            switch (bulkMode){
                case BulkMode.BulkMaster:
                    sql = "TRUNCATE TABLE " + DBName + ".dbo.tbl_BulkMaster";
                    break;
                case BulkMode.BulkDetail:
                    sql = "TRUNCATE TABLE " + DBName + ".dbo.tbl_BulkDetail";
                    break;
            }using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(sql, cn);
                    cmd.ExecuteNonQuery();
                }catch (Exception e){
                    string errMsg = e.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
        }

        private void DeleteRequest(string CLNumber, RequestMode requestMode) {
            string DBName = DBUtilities.GetDBName("devCon");
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));

            string sql = "";
            string tblName = "";
            string qWhere = " WHERE (CLNum = @CLNum)";
            switch (requestMode){
                case RequestMode.Master:
                    tblName = "tbl_Request";
                    break;
                case RequestMode.Approval:
                    tblName = "tbl_RequestApproval";
                    break;
                case RequestMode.Customer:
                    tblName = "tbl_RequestCustomer";
                    break;
                case RequestMode.Product:
                    tblName = "tbl_RequestProduct";
                    break;
                case RequestMode.Files:
                    tblName = "tbl_RequestFiles";
                    break;
            }
            
            using(cn){
                try{
                    cn.Open();

                    sql = "DELETE FROM " + DBName + ".dbo." + tblName + qWhere;
                    SqlCommand cmd = new SqlCommand(sql, cn);
                    cmd.Parameters.AddWithValue("CLNum", CLNumber);
                    cmd.ExecuteNonQuery();
                }catch (Exception e){
                    string errMsg = e.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
        }

        private void DeletePrevFile(){
            string[] oldFile = lblPrevFile.Text.Split('\\');
            string path = Server.MapPath("~/images/upload/" + oldFile[oldFile.Count() - 1].ToString());
            FileInfo prevFile = new FileInfo(path);
            if (prevFile.Exists){
                string dbName = DBUtilities.GetDBName("devCon");
                string qry = "DELETE FROM " + dbName + ".dbo.tbl_RequestFiles WHERE " +
                    "(CLNum = @CLNum)";
                SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
                using (cn){
                    try{
                        cn.Open();

                        SqlCommand cmd = new SqlCommand(qry, cn);
                        cmd.Parameters.AddWithValue("@CLNum", ID);
                        if (cmd.ExecuteNonQuery() > 0){
                            prevFile.Delete();
                        }
                    }catch (Exception ex){
                        string ErrMsg = ex.Message;
                    }finally{
                        if (cn.State == ConnectionState.Open){
                            cn.Close();
                        }
                    }
                }
            }
        }

        private static string GetFileExtension(string fileType){
            string result = "";
            switch (fileType){
                case "application/vnd.ms-excel":
                    result = "xls";
                    break;
                case "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet":
                    result = "xlsx";
                    break;
                default:
                    result = "NULL";
                    break;
            }
            return result;
        }

        private static string setExcelConn(string fileName, string fileType){
            return setExcelConn(fileName, fileType, true);
        }

        private static string setExcelConn(string fileName, string fileType, bool HDR){
            string result = "INVALID";
            if ((fileName != null || fileName != string.Empty) &&
                (fileType != null || fileType != string.Empty)){

                string incHDR = (HDR == true) ? ";HDR=YES" : "";
                try{
                    switch (fileType){
                        case "application/vnd.ms-excel":
                            result = "Provider=Microsoft.Jet.OLEDB.4.0;";
                            result += "Data Source=" + fileName + ";";
                            result += "Extended Properties='Excel 8.0" + incHDR + "'";
                            break;
                        case "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet":
                            result = "Provider=Microsoft.ACE.OLEDB.12.0;";
                            result += "Data Source=" + fileName + ";";
                            result += "Extended Properties='Excel 8.0" + incHDR + "'";
                            break;
                        default:
                            result = "INVALID";
                            break;
                    }
                }catch (Exception ex){
                    result = ex.Message;
                }
            }else{
                result = string.Empty;
            }
            return result;
        }

        private static string RenameIfExist(string fileName)
        {
            string result = string.Empty;
            string[] nmFile = fileName.Split(new string[] { "\\" }, StringSplitOptions.None);
            string[] tmpFile = fileName.Split(new string[] { "." }, StringSplitOptions.None);
            if (File.Exists(HttpContext.Current.Server.MapPath("~/images/upload/" + nmFile[nmFile.Count() - 1])))
            {
                Random rnd = new Random();
                int numExt = rnd.Next(1, 9999);
                string sExt = numExt.ToString().PadLeft(4, '0');

                var tmpResult = "";
                int arrLen = tmpFile.Length;
                for (var idx = 0; idx < arrLen; idx++)
                {
                    if (idx < (arrLen - 1))
                    {
                        tmpResult += tmpFile[idx].ToString();
                    }
                    else if (idx == (arrLen - 1))
                    {
                        tmpResult += sExt + "." + tmpFile[idx].ToString();
                    }
                }
                result = tmpResult;
            }
            else
            {
                result = fileName;
            }
            return result;
        }

        protected void SetBulkStatus(int BulkStatus){
            string DBName = DBUtilities.GetDBName("devCon");
            string strCon = DBUtilities.strConnection("devCon");
            SqlConnection cn = new SqlConnection(strCon);
            string sql = "UPDATE " + DBName + ".dbo.tbl_BulkProcess " +
                "SET BulkProgress = " + BulkStatus + " WHERE " +
                "IDSeq = 1";
            using (cn){
                try{
                    cn.Open();

                    SqlCommand cmd = new SqlCommand(sql, cn);
                    cmd.ExecuteNonQuery();
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
        }

        private static int ExecQuery(string query){
            int result = 0;
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(query, cn);
                    result = cmd.ExecuteNonQuery();
                }catch (Exception e){
                    string errMsg = e.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static int ExecQuery(string query, Dictionary<string, object> Params)
        {
            int result = 0;
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(query, cn);
                    foreach (KeyValuePair<string, object> kvp in Params){
                        cmd.Parameters.AddWithValue(kvp.Key, kvp.Value);
                    }
                    result = cmd.ExecuteNonQuery();
                }catch (Exception e){
                    string errMsg = e.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        protected static bool isBulkUploading(){
            bool result = true;

            string strCon = DBUtilities.strConnection("DevCon");
            SqlConnection sqlCon = new SqlConnection(strCon);

            using (sqlCon){
                sqlCon.Open();

                string strQuery = "SELECT BulkProgress FROM dbo.tbl_BulkProcess WHERE IDSeq = 1";
                SqlCommand sqlCmd = new SqlCommand(strQuery, sqlCon);
                SqlDataReader sqlReader = sqlCmd.ExecuteReader();
                if (sqlReader.HasRows){
                    if (sqlReader.Read()){
                        int tmp = Convert.ToInt32(sqlReader.GetValue(0).ToString());
                        if (tmp == 0){
                            result = false;
                        }
                    }
                }
                sqlCon.Close();
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static List<object> GetApprovalList(int ApprovalLayer, string CurrentApproval){

            List<object> result = new List<Object>();
            string xs = "";
            Dictionary<string, string> dctAppStep = new Dictionary<string, string>();
            if (string.IsNullOrEmpty(CurrentApproval)){
                dctAppStep = AppStepList(ApprovalLayer);
            }else{
                dctAppStep = AppStepList(ApprovalLayer, CurrentApproval);
            }

            string sRole = "";
            string sUname = "";
            foreach (KeyValuePair<string, string> oAppList in dctAppStep){
                if (oAppList.Key == "ROLE"){
                    sRole = oAppList.Value;
                }else if (oAppList.Key == "UNAME"){
                    sUname = oAppList.Value;
                }else{
                    xs = oAppList.Value;
                }
            }

            string xRole = (string.IsNullOrEmpty(sRole)) ? "'0'" : sRole;
            string xUname = (string.IsNullOrEmpty(sUname)) ? "''" : sUname;

            string qry = "SELECT UserName, FullName " +
                "FROM [dbo].[UserAccounts] WHERE (AppActor = 'Y') AND (StatusCode = '101') AND " +
                "((IDRole IN (" + xRole + ")) OR " +
                "(UserName IN (" + xUname + "))) " +
                "ORDER BY FullName";

            result = AppUserList(qry);
            return result;
        }

        private static Dictionary<string, string> AppStepList(int ApprovalLayer){

            Dictionary<string, string> result = new Dictionary<string, string>();
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                cn.Open();

                string qry = "SELECT NextStep FROM [dbo].[tbl_ApprovalStep] " +
                    "WHERE (IDControl = 'CHAN') AND (Value1 = 'HOS') " +
                    "AND (Value2 = '') " + "AND (StepSeq = " + ApprovalLayer + ") " +
                    "GROUP BY NextStep";
                SqlCommand cmd = new SqlCommand(qry, cn);
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows){
                    int zro = 0;
                    List<string> lstResultInt = new List<string>();
                    List<string> lstResultStr = new List<string>();
                    while (rdr.Read()){
                        string tmpResult = rdr.GetValue(0).ToString();

                        if (int.TryParse(tmpResult, out zro)){
                            lstResultInt.Add(tmpResult);
                        }else{
                            lstResultStr.Add("'" + tmpResult + "'");
                        }
                    }
                    string sRole = "0";
                    string sUname = "''";

                    if ((lstResultInt.Count) > 0){
                        string[] strRole = lstResultInt.ToArray();
                        sRole = string.Join(",", strRole);
                    }

                    if ((lstResultStr.Count) > 0){
                        string[] strUname = lstResultStr.ToArray();
                        sUname = string.Join(",", strUname);
                    }
                    result.Add("ROLE", sRole);
                    result.Add("UNAME", sUname);
                }
                cn.Close();
            }
            return result;
        }

        private static Dictionary<string, string> AppStepList(int ApprovalLayer, string CurrentApproval)
        {

            Dictionary<string, string> result = new Dictionary<string, string>();
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                cn.Open();
                string xtra = " AND ((CurrentStep = '" + CurrentApproval + "') OR ";
                xtra += "(CurrentStep = CONVERT(NVARCHAR,(SELECT IDRole FROM Ebiz.dbo.UserAccounts WHERE UserName = '" + CurrentApproval + "')))) ";
                string qry = "SELECT NextStep FROM [dbo].[tbl_ApprovalStep] " +
                    "WHERE (IDControl = 'CHAN') AND (Value1 = 'HOS') " +
                    "AND (Value2 = '') " + "AND (StepSeq = " + ApprovalLayer + ") " + xtra +
                    "GROUP BY NextStep";

                SqlCommand cmd = new SqlCommand(qry, cn);
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows){
                    int zro = 0;
                    List<string> lstResultInt = new List<string>();
                    List<string> lstResultStr = new List<string>();
                    while (rdr.Read()){
                        string tmpResult = rdr.GetValue(0).ToString();

                        if (int.TryParse(tmpResult, out zro)){
                            lstResultInt.Add(tmpResult);
                        }else{
                            lstResultStr.Add("'" + tmpResult + "'");
                        }
                    }
                    string sRole = "0";
                    string sUname = "''";

                    if ((lstResultInt.Count) > 0){
                        string[] strRole = lstResultInt.ToArray();
                        sRole = string.Join(",", strRole);
                    }

                    if ((lstResultStr.Count) > 0){
                        string[] strUname = lstResultStr.ToArray();
                        sUname = string.Join(",", strUname);
                    }

                    result.Add("ROLE", sRole);
                    result.Add("UNAME", sUname);
                }
                cn.Close();
            }
            return result;
        }

        private static List<object> AppUserList(string Query){
            List<object> result = new List<object>();
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                cn.Open();

                SqlCommand cmd = new SqlCommand(Query, cn);
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows){
                    while (rdr.Read()){
                        string dctKey = rdr.GetValue(0).ToString();
                        string dctVal = rdr.GetValue(1).ToString();

                        Dictionary<string, object> dctResult = new Dictionary<string, object>();
                        dctResult.Add("appKey", dctKey);
                        dctResult.Add("appVal", dctVal);

                        result.Add(dctResult);
                    }
                }
                cn.Close();
            }
            return result;
        }
    }
}
